package databaseQueries;

import helper.IOFile;

import java.sql.*;
import java.util.ArrayList;

public class DatabaseQueries {

	static final String KIND_PROGRAM_FILE_NAME = "data/Kind_Program.txt";
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost/tv_schedule";

	// Database credentials
	static final String USER_MYSQL = "root";
	static final String PASS_MYSQL = "123456";

   /* Create table in Mysql database*/ 
    public void createTbl(String sqlCreateTable) {
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);

			stmt = conn.createStatement();
			stmt.executeUpdate(sqlCreateTable);

			stmt.close();
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			}// nothing we can do
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}// end finally try
		}// end try
	}
    
    public void createTablDatabase(){
    	// Set of create table queries
    	 String sqlCreateTabl_Kind = "CREATE TABLE Kind " + "(id_kind INTEGER not NULL AUTO_INCREMENT, "
    			+ " name_kind VARCHAR(255) not NULL, " + " PRIMARY KEY ( id_kind ))";

    	 String sqlCreateTabl_Chanel = "CREATE TABLE Channel " + "(id_channel INTEGER not NULL AUTO_INCREMENT, "
    			+ " name_channel VARCHAR(255) not NULL, url_channel VARCHAR(255), kind_channel VARCHAR(255)," +
    			"publisher_channel VARCHAR(255), des_channel VARCHAR(255), " + " PRIMARY KEY ( id_channel ))";
    	
    	 String sqlCreateTabl_KindChanel = "CREATE TABLE KindChannel " + "(id_kindchannel INTEGER not NULL AUTO_INCREMENT, "
    			+ " name_kindchannel VARCHAR(255) not NULL, id_kind VARCHAR(255) not NULL,id_channel VARCHAR(255) not NULL," +
    			" " + " PRIMARY KEY ( id_kindchannel ))";
    	
    	 String sqlCreateTabl_Program = "CREATE TABLE Program " + "(id_program INTEGER not NULL AUTO_INCREMENT, "
    			+ " name_program VARCHAR(255), id_kind VARCHAR(255)," +
    			" " + " PRIMARY KEY ( id_program ))";
        
    	 String sqlCreateTabl_Schedule =  "CREATE TABLE Schedule " + "(id_schedule INTEGER not NULL AUTO_INCREMENT, "
    			+ " start_time VARCHAR(255), duration VARCHAR(255), end_time VARCHAR(255), date VARCHAR(255) not NULL," +
    			"program_id INTEGER NOT NULL, channel_id INTEGER not NULL, day_of_week VARCHAR(255)," +
    			" " + " PRIMARY KEY ( id_schedule ))";
    	// create all table in database
    	createTbl(sqlCreateTabl_Kind);
    	createTbl(sqlCreateTabl_KindChanel);
    	createTbl(sqlCreateTabl_Chanel);
    	createTbl(sqlCreateTabl_Program);
    	createTbl(sqlCreateTabl_Schedule);
    }
    /* End Create table in Mysql database*/ 

    /* Insert data into table program Kind*/
    public void insertTableKind(String kindName){
    	Connection conn = null;
    	PreparedStatement preparedStmt = null;
    	String stringSql =  "INSERT INTO kind (name_kind)"
    	        + " values (?)";
    	try{
    		Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			preparedStmt = conn.prepareStatement(stringSql);
		    preparedStmt.setString (1, kindName);
		    // execute the preparedstatement
		    preparedStmt.execute();		      
		    conn.close();
    	}catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    }
    public void processInsertTableKind(){
    	IOFile ioFile = new IOFile();
    	ArrayList<String> arrayKindString = ioFile.readFileToArray(KIND_PROGRAM_FILE_NAME, -1);
    	for(int i=0;i< arrayKindString.size();i++){
    		String nameKind = arrayKindString.get(i);
    		insertTableKind(nameKind);
    	}
    }
    /* End Insert data into table program Kind*/
    
    /*Check channel is exist*/
    public boolean checkChannelNotExist(String nameChannel, String publisher){
    	Connection conn = null;
    	ResultSet resultSet = null;
    	Statement statement = null;
    	String stringSql = "SELECT id_channel FROM channel where name_channel = '"+nameChannel+"' and " +
    			"publisher_channel ='"+publisher+"';";
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			statement = conn.createStatement();
			resultSet = statement.executeQuery(stringSql);
			while (resultSet.next()) {
				return false;
			}
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    	return true;
    }
    /*End Check channel is exist*/
    
    /* Insert data into table Channel*/
    public void insertTableChannel(String nameChannel,String URLChannel,String kindChannel,String publisher,String des){
    	Connection conn = null;
    	PreparedStatement preparedStmt = null;
    	String stringSql =  "INSERT INTO channel (name_channel,url_channel,kind_channel,publisher_channel,des_channel)"
    	        + " values (?,?,?,?,?)";
    	try{
    		Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			preparedStmt = conn.prepareStatement(stringSql);
		    preparedStmt.setString (1, nameChannel);
		    preparedStmt.setString (2,  URLChannel);
		    preparedStmt.setString (3, kindChannel);
		    preparedStmt.setString (4, publisher);
		    preparedStmt.setString (5, des);
		    // execute the preparedstatement
		    preparedStmt.execute();		      
		    conn.close();
    	}catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    }
    /*End Insert data into table Channel*/
    
    /* Insert data into table Program*/
    public void insertTableProgram(String nameProgram,int kindID){
    	Connection conn = null;
    	PreparedStatement preparedStmt = null;
    	String stringSql =  "INSERT INTO program (name_program,id_kind)"
    	        + " values (?,?)";
    	try{
    		Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			preparedStmt = conn.prepareStatement(stringSql);
		    preparedStmt.setString (1, nameProgram);
		    preparedStmt.setInt (2,  kindID);
		   
		    // execute the preparedstatement
		    preparedStmt.execute();		      
		    conn.close();
    	}catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    }
    /*End Insert data into table program*/
    
    /* Insert data into table Schedule*/
    public void insertTableSchedule(String startTime, String duration, String endTime, 
    		String date, String dayOfWeek,int programID,int channelID){
    	Connection conn = null;
    	PreparedStatement preparedStmt = null;
    	String stringSql =  "INSERT INTO Schedule (start_time,duration,end_time,date," +
    			"day_of_week,program_id,channel_id)"
    	        + " values (?,?,?,?,?,?,?)";
    	try{
    		Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			preparedStmt = conn.prepareStatement(stringSql);
		    preparedStmt.setString (1, startTime);
		    preparedStmt.setString (2,  duration);
		    preparedStmt.setString (3,  endTime);
		    preparedStmt.setString (4,  date);
		    preparedStmt.setString (5,  dayOfWeek);
		    preparedStmt.setInt (6,  programID);
		    preparedStmt.setInt (7,  channelID);
		       
		    // execute the preparedstatement
		    preparedStmt.execute();		      
		    conn.close();
    	}catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    }
    /*End Insert data into table schedule*/
    
    /*Check schedule is exist*/
    public boolean checkScheduleNotExist(int channelID, int programID, String date){
    	Connection conn = null;
    	ResultSet resultSet = null;
    	Statement statement = null;
    	String stringSql = "SELECT id_schedule FROM schedule where channel_id = '"+channelID+"' and " +
    			"program_id ='"+programID+"' and date = '"+date+"';";
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			statement = conn.createStatement();
			resultSet = statement.executeQuery(stringSql);
			while (resultSet.next()) {
				return false;
			}
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    	return true;
    }
    /*End Check schedule is exist*/
    
    /* Get Kind id from Kind name in Database */
    public int getKindID(String kindName){
    	int kindID =-1;
    	Connection conn = null;
    	ResultSet resultSet = null;
    	Statement statement = null;
    	String stringSql = "SELECT id_kind FROM kind WHERE name_kind = '"+kindName+"';";
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			statement = conn.createStatement();
			resultSet = statement.executeQuery(stringSql);
			while (resultSet.next()) {
				kindID = resultSet.getInt("id_kind");
			}
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    	return kindID;
    }
    /* End Get Kind id from Kind name in Database */
    
    /* Get Program id from program name in Database */
    public int getProgramID(String programName){
    	int programID =-1;
    	Connection conn = null;
    	ResultSet resultSet = null;
    	Statement statement = null;
    	String stringSql = "SELECT id_Program FROM program WHERE name_Program = '"+programName+"';";
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			statement = conn.createStatement();
			resultSet = statement.executeQuery(stringSql);
			while (resultSet.next()) {
				programID = resultSet.getInt("id_Program");
			}
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    	return programID;
    }
    /* End Get Program id from Program name in Database */
    
    /*Get Channel id from Channel name in Database */
    public int getChannelID(String channelName){
    	int channelID =-1;
    	Connection conn = null;
    	ResultSet resultSet = null;
    	Statement statement = null;
    	String stringSql = "SELECT id_Channel FROM channel WHERE name_channel = '"+channelName+"';";
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, USER_MYSQL, PASS_MYSQL);
			statement = conn.createStatement();
			resultSet = statement.executeQuery(stringSql);
			while (resultSet.next()) {
				channelID = resultSet.getInt("id_Channel");
			}
			conn.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
    	return channelID;
    }   
    /*End Get Channel id from Channel name in Database */
    public static void main(String[] args){
    	DatabaseQueries dbq = new DatabaseQueries();
    	//dbq.createTablDatabase();
    	//dbq.processInsertTableKind();
    	
    }

}
